﻿using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using productWebApi.Models;
using productWebApi.Models.helpModels;
using productWebApi.Models.searchModels;
using ShopeeWebApi.AmazonBase;
using ShopeeWebApi.Model;
using ShopeeWebApi.Model.Enum;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

using Sy;

using Attribute = ShopeeWebApi.Model.Attribute;
using System.Text.RegularExpressions;

namespace ShopeeWebApi.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class AmazonController : ControllerBase
    {
        private readonly ILogger<AmazonController> logger;
        private ProductNodeDBContext DBContext;

        public AmazonController(ProductNodeDBContext _DBContext, ILogger<AmazonController> _logger)
        {
            DBContext = _DBContext;
            logger = _logger;
        }
        /// <summary>
        /// 第一步：上传excel文件
        /// </summary>
        /// <param name="files"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("/Amazon/UploadHtmlFile")]
        public async Task<IActionResult> UploadHtmlFile(List<IFormFile> files)
        {
            try
            {
                long size = files.Sum(f => f.Length);
                foreach (var formFile in files)
                {
                    if (formFile.Length > 0)
                    {
                        using (var memoryStream = new MemoryStream())
                        {
                            await formFile.CopyToAsync(memoryStream);
                            if (memoryStream.Length <= 2097152)
                            {
                                var file = new RealShipRaw()
                                {
                                    Name = DBExcute.GetFileName(formFile.FileName),
                                    FileContent = memoryStream.ToArray(),
                                };
                                var existFile = DBContext.RealShipRaw.Where(x => x.Name.Contains(file.Name)).FirstOrDefault();
                                if (existFile != null)
                                {
                                    if(existFile.Name.ToLower().Contains("com.mx"))
                                    {
                                        existFile.BTGFileContent = file.FileContent;
                                        existFile.URL = "";
                                        existFile.Area = "";
                                        DBContext.RealShipRaw.Update(existFile);
                                    }
                                    else
                                    {
                                        existFile.FileContent = file.FileContent;
                                        existFile.URL = "";
                                        existFile.Area = "";
                                        DBContext.RealShipRaw.Update(existFile);
                                    }                                   
                                }
                                else
                                {
                                    DBContext.RealShipRaw.Add(file);
                                }
                                await DBContext.SaveChangesAsync();
                            }
                            else
                            {
                                return new JsonResult("{'result':'-1','msg':'文件超过了2MB'}");
                            }
                        }
                    }
                }
                return new JsonResult("{'result':1,'msg':'上传成功'}");
            }
            catch (Exception e)
            {
                logger.LogError("上传HTML文件失败，原因：" + e.Message);
                return new JsonResult("{'result':'-2','msg':'上传失败'}");
            }
        }


        [HttpGet]
        [Route("/Amazon/ShowHtmlFiles")]
        public IEnumerable<RealShipRawAll> ShowHtmlFiles()
        {
            var Data = DBContext.NodeRaw.Where(w => w.Length == 0).GroupBy(a => a.RealShipRaw.ID)
                .Select(
                g => new
                {
                    ID = g.Key,
                    count = g.Count()
                }).Concat(DBContext.ProductRaw.Where(w => w.Length == 0).GroupBy(a => a.RealShipRaw.ID)
                .Select(
                g => new
                {
                    ID = g.Key,
                    count = g.Count()
                })).GroupBy(b => b.ID).Select(s => new
                {
                    ID = s.Key,
                    sumCount = s.Sum(i => i.count)
                });

            var d = from n in DBContext.RealShipRaw
                    join a in Data on n.ID equals a.ID
                    into re
                    from r in re.DefaultIfEmpty()
                    select new RealShipRawAll
                    {
                        ID = n.ID,
                        Name = n.Name,
                        URL = n.URL,
                        Area = n.Area,
                        Count = r.sumCount
                    };
            return d.ToArray();
        }



        [HttpGet]
        [Route("/Amazon/ShowRealShipByIdN")]
        public pagingModel<NodeRaw> ShowRealShipByIdN(int id, int currentPage = 0, int pagesize = 10)
        {
            int totalCounts = DBContext.NodeRaw.Where(x => x.RealShipRaw.ID == id).Count();
            var items = DBContext.NodeRaw.Where(x => x.RealShipRaw.ID == id).OrderBy(o => o.Length).ThenByDescending(t => t.AnsiTime)
                .Skip(pagesize * (currentPage - 1)).Take(pagesize).Select(s => new NodeRaw
                {
                    ID = s.ID,
                    Name = s.Name,
                    Length = s.Length,
                    GetTime = s.GetTime,
                    AnsiTime = s.AnsiTime,
                    URL = s.URL,
                });
            pagingModel<NodeRaw> myPageModel = new pagingModel<NodeRaw>()
            {
                PageSize = pagesize,
                CurrentPage = currentPage,
                TotalCount = totalCounts,
                PageCount = totalCounts % pagesize > 0 ? totalCounts / pagesize + 1 : totalCounts / pagesize,
                Lists = items.AsQueryable()
            };
            return myPageModel;
        }


        [HttpGet]
        [Route("/Amazon/ShowRealShipByIdP")]
        public pagingModel<ProductRaw> ShowRealShipByIdP(int id, int currentPage = 0, int pagesize = 10)
        {
            int totalCounts = DBContext.ProductRaw.Where(x => x.RealShipRaw.ID == id).Count();
            var items = DBContext.ProductRaw.Where(x => x.RealShipRaw.ID == id).OrderBy(o => o.Length).ThenBy(t => t.AnsiTime)
                .Skip(pagesize * (currentPage - 1)).Take(pagesize).Select(s => new ProductRaw
                {
                    ID = s.ID,
                    Name = s.Name,
                    Length = s.Length,
                    GetTime = s.GetTime,
                    AnsiTime = s.AnsiTime,
                    URL = s.URL,
                });

            pagingModel<ProductRaw> myPageModel = new pagingModel<ProductRaw>()
            {
                PageSize = pagesize,
                CurrentPage = currentPage,
                TotalCount = totalCounts,
                PageCount = totalCounts % pagesize > 0 ? totalCounts / pagesize + 1 : totalCounts / pagesize,
                Lists = items.AsQueryable()
            };
            return myPageModel;
        }

        [HttpGet]
        [Route("/Amazon/ShowRealShipById")]
        public IEnumerable<RealShipRawDetail> ShowRealShipById(int id)
        {
            IEnumerable<RealShipRawDetail> realShipRawDetailsp = DBContext.ProductRaw
                .Where(x => x.RealShipRaw.ID == id && x.Length == 0)
                 .OrderByDescending(o => o.Length).Select(s => new RealShipRawDetail
                 {
                     ID = s.ID,
                     RealShipRawID = id,
                     Type = "p",
                     URL = s.URL
                 });

            IEnumerable<RealShipRawDetail> realShipRawDetailsn = DBContext.NodeRaw
                .Where(x => x.RealShipRaw.ID == id && x.Length == 0)
                .OrderByDescending(o => o.Length).Select(s => new RealShipRawDetail
                {
                    ID = s.ID,
                    RealShipRawID = id,
                    Type = "n",
                    URL = s.URL
                });
            return realShipRawDetailsp.Union(realShipRawDetailsn);
        }

        /// <summary>
        /// 第二步：解析html，提取node，product
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet]
        [Route("/Amazon/AnalysisHtml")]
        public IActionResult AnalysisHtml(int id)
        {
            var RealShipRaw = DBContext.RealShipRaw.Where(x => x.ID == id).FirstOrDefault();
            if (RealShipRaw.IsNotNull())
            {
                //解析所属国家或地区
                Sy.String Page = Encoding.UTF8.GetString(RealShipRaw!.FileContent!);  
                const string AreaSpliter = "'Events.SushiEndpoint': '";
                if (Page.Contains(AreaSpliter))
                {
                    RealShipRaw.Area = Page.SplitEx(AreaSpliter, 1).Mid("amazon.", "/");
                }
                else
                {
                    return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundAreaSpliter]'}");
                }
                //根据国家或地区，找到不同配置：tableIndex，nameIndex，BTGIndex 分类树，CCTIndex 模板
                short tableIndex = -1, nameIndex = -1, BTGIndex = -1, CCTIndex = -1;
                switch (RealShipRaw.Area)
                {
                    case "ca":
                        tableIndex = -1;
                        nameIndex = 0;
                        BTGIndex = 2;
                        CCTIndex = 1;
                        break;
                    case "co.uk":  //欧洲五国
                        tableIndex = -1;
                        nameIndex = 0;
                        CCTIndex = 0;
                        BTGIndex = 1;
                        break;
                    case "com":
                        tableIndex = -1;
                        nameIndex = 0;
                        CCTIndex = 0;
                        BTGIndex = 2;
                        break;
                    case "com.mx":   //墨西哥
                        tableIndex = -1;
                        nameIndex = 0;
                        CCTIndex = 1;
                        BTGIndex = 0;
                        break;
                    case "co.jp":
                        tableIndex = 3;
                        nameIndex = 0;
                        CCTIndex = 1;
                        BTGIndex = 3;
                        break;
                    default://ae,nl.pl.se
                        tableIndex = -1;
                        nameIndex = 0;
                        BTGIndex = 1;
                        CCTIndex = 0;
                        break;
                }
                if(RealShipRaw.Area== "com.mx")
                {
                   return SeparatelyUploaded(id, RealShipRaw, tableIndex, nameIndex, BTGIndex, CCTIndex);
                }
                else
                {
                    return WholeUpload(id, RealShipRaw, Page, tableIndex, nameIndex, BTGIndex, CCTIndex);
                }               
            }
            else
            {
                return new JsonResult("{'result':'-1','msg':'记录不存在'}");
            }
        }

        private IActionResult WholeUpload(int id, RealShipRaw? RealShipRaw, Sy.String Page, short tableIndex, short nameIndex, short BTGIndex, short CCTIndex)
        {
            var TableMidor = new Midor("<table", "</table>");
            string[] str = new string[] { "<table", "</table>" };
            if (Page.CanMid(TableMidor))
            {
                Sy.String TableContent = null;
                switch (tableIndex)
                {
                    case 3:  //取第三个table
                        TableContent = Page.SplitEx(str, 5);
                        //Page.Mid(TableMidor, Sy.StringMidOptions.StartFormFront_EndFormFront);
                        break;
                    case -1:  //取最后一个table
                        TableContent = Page.Mid(TableMidor, Sy.StringMidOptions.StartFormBack_EndFormFront);
                        break;
                }

                var RowMidor = new Midor("<tr", "</tr>");
                var aMidor = new Midor("<a", "</a>");
                var nMidor = new Midor(">", "</a>");
                if (TableContent!.CanMid(RowMidor))
                {
                    var Enumerator = TableContent.GetMidEnumerator(RowMidor);
                    Dictionary<string, Sy.String[]> dicRowUseful = new();
                    Dictionary<string, Sy.String[]> dicRowRewRefUseful = new();
                    if (Enumerator.MoveNext())
                    {
                        //解析Thead->Tr->Th                      
                        const string TdEnd = "</td>";
                        const string aEnd = "</a>";
                        Dictionary<int, int> NeedSpan = new();
                        string name = "";
                        while (Enumerator.MoveNext())
                        {
                            if (Enumerator.Current.Contains(aEnd))
                            {
                                var Temp = Enumerator.Current.Split(TdEnd);
                                if (NeedSpan.Count > 0)
                                {
                                    var Lst = Temp.ToList();
                                    List<int> LstDel = new();
                                    foreach (var key in NeedSpan.Keys.OrderBy((i) => i))
                                    {
                                        var LastNum = NeedSpan[key];
                                        Lst.Insert(key, null);
                                        if (--LastNum == 0)
                                        {
                                            LstDel.Add(key);
                                        }
                                        else

                                        {
                                            NeedSpan[key] = LastNum;
                                        }
                                    }
                                    Temp = Lst.ToArray();
                                    LstDel.ForEach(i => NeedSpan.Remove(i));
                                }

                                if (Temp[nameIndex] is not null)
                                {
                                    if (Temp[nameIndex].Contains("</a>"))
                                    {
                                        name = Temp[nameIndex].Mid("<a", "</a>").Split('>')[1];
                                    }
                                    else if (Temp[nameIndex].Contains("<strong>"))
                                    {
                                        name = Temp[nameIndex].Mid("<strong>", "</strong>");
                                    }
                                    else
                                    {
                                        name = Temp[nameIndex].Replace("<td>", "").Replace("\r\n", "").Replace(">", "").Trim();
                                    }
                                    dicRowUseful.Add(name, Temp);
                                }

                                const string Span = "rowspan=\"";
                                for (short i = 0; i < Temp.Length; i++)
                                {
                                    var td = Temp[i];
                                    //只检索cct和btg列                                       
                                    if (td is not null && td.Contains(Span))
                                    {
                                        var numstr = td.Mid(Span);
                                        if (int.TryParse(numstr, out var num))
                                        {
                                            NeedSpan.Add(i, --num);
                                        }
                                        else
                                        {
                                            return new JsonResult("{'result':'-1','msg':'解析内容出错[InvalidCast]'}");
                                        }
                                    }
                                }
                            }
                        };

                        if (dicRowUseful.Count > 0)
                        {
                            List<RealShip> RealShips = new();
                            List<NodeRaw> NodeRaws = new();
                            List<ProductRaw> ProductRaws = new();
                            NodeRaw LastNodeRaw = null;
                            ProductRaw LastProductRaw = null;
                            string btgStr = "", cctStr = "";
                            foreach (var Cols in dicRowUseful)
                            {
                                const string Href = "href=\"";
                                Dictionary<string, ProductRaw> multiProducts = new();
                                List<NodeRaw> multiNodes = new List<NodeRaw>();
                                var keyName = Cols.Key;
                                var BTG = Cols.Value[BTGIndex];
                                if (BTG.IsNotNull())
                                {
                                    var count = BTG.Split(aEnd).Length - 1;
                                    if (count == 1)
                                    {
                                        NodeRaws.Add(LastNodeRaw = new NodeRaw(RealShipRaw, BTG.Mid(Href)));
                                    }
                                    else if (count > 1)
                                    {
                                        var aEnumerator = BTG.GetMidEnumerator(aMidor);
                                        while (aEnumerator.MoveNext())
                                        {
                                            var aTemp = aEnumerator.Current;
                                            name = aTemp.Split('>')[1].Trim();
                                            if (!"此处".Equals(name) && !"目录".Equals(name))
                                            {
                                                multiNodes.Add(new NodeRaw(RealShipRaw, aTemp.Mid(Href)));
                                            }
                                        }
                                        if (multiNodes.Count() == 1)
                                        {
                                            LastNodeRaw = multiNodes.First();
                                        }
                                    }
                                    else if (BTG.Contains('“'))
                                    {
                                        dicRowRewRefUseful.Add(Cols.Key, Cols.Value);
                                        LastNodeRaw = null;
                                    }
                                    else
                                    {
                                        LastNodeRaw = null;
                                    }
                                }
                                var CCT = Cols.Value[CCTIndex];
                                if (CCT.IsNotNull())
                                {
                                    var count = CCT.Split(aEnd).Length - 1;
                                    if (count == 1)
                                    {
                                        ProductRaws.Add(LastProductRaw = new ProductRaw(RealShipRaw, CCT.Mid(Href)));
                                    }
                                    else if (count > 1)
                                    {
                                        var aLinkMidor = new Midor("<a", "</a>");
                                        var aEnumerator = CCT.GetMidEnumerator(aLinkMidor);
                                        while (aEnumerator.MoveNext())
                                        {
                                            var aTemp = aEnumerator.Current;
                                            name = aTemp.Split('>')[1].Trim();
                                            if (!"此处".Equals(name) && !"目录".Equals(name))
                                            {
                                                multiProducts.Add(name, new ProductRaw(RealShipRaw, aTemp.Mid(Href)));
                                            }
                                        }
                                        if (multiProducts.Count() == 1)
                                        {
                                            LastProductRaw = multiProducts.First().Value;
                                        }
                                    }
                                    else if (CCT.Contains('“'))
                                    {
                                        dicRowRewRefUseful.Add(Cols.Key, Cols.Value);
                                        LastProductRaw = null;
                                    }
                                    else
                                    {
                                        LastProductRaw = null;
                                    }
                                }
                                if (LastProductRaw is null && LastNodeRaw is null)
                                {
                                    continue;
                                }
                                //二者，最多只有一方是多条，另一方必定是一条记录
                                if (multiNodes.Count() >= 1)
                                {
                                    foreach (var node in multiNodes)
                                    {
                                        RealShips.Add(new(RealShipRaw, node!, LastProductRaw!, Cols.Key));
                                    }
                                }
                                else if (multiProducts.Count() >= 1)
                                {
                                    foreach (var product in multiProducts)
                                    {
                                        RealShips.Add(new(RealShipRaw, LastNodeRaw!, product.Value!, product.Key));
                                    }
                                }
                                else
                                {
                                    RealShips.Add(new(RealShipRaw, LastNodeRaw!, LastProductRaw!, Cols.Key));
                                }
                            }
                            DBContext.AddRange(RealShips.ToArray());
                            DBContext.SaveChanges();
                        }
                        else
                        {
                            return new JsonResult("{'result':'-1','msg':'[no data]'}");
                        }

                        if (dicRowRewRefUseful.Count > 0)
                        {
                            List<RealShip> UpdateRealShips = new();
                            foreach (var Cols in dicRowRewRefUseful)
                            {
                                var keyName = Cols.Key;
                                var refRealShip = DBContext.RealShip.Where(x => x.RealShipRaw.ID == id && x.Name == keyName).FirstOrDefault();
                                if (refRealShip is not null)
                                {
                                    var BTG = Cols.Value[BTGIndex];
                                    if (BTG.IsNotNull())
                                    {
                                        if (BTG.Contains('“'))
                                        {
                                            string refkeyname = BTG.Mid("“", "”");
                                            var nodeRaw = DBContext.RealShip.Where(x => x.RealShipRaw.ID == id && x.Name == refkeyname).FirstOrDefault();
                                            if (nodeRaw is not null)
                                            {
                                                refRealShip.NodeRaw = nodeRaw.NodeRaw;
                                            }
                                        }
                                    }
                                    var CCT = Cols.Value[CCTIndex];
                                    if (CCT.IsNotNull())
                                    {
                                        if (CCT.Contains('“'))
                                        {
                                            string refkeyname = CCT.Mid("“", "”");
                                            var productRaw = DBContext.RealShip.Where(x => x.RealShipRaw.ID == id && x.Name == refkeyname).Select(x => x.ProductRaw).FirstOrDefault();
                                            if (productRaw is not null)
                                            {
                                                refRealShip.ProductRaw = productRaw;
                                            }
                                        }
                                    }
                                    UpdateRealShips.Add(refRealShip);
                                }
                                else
                                {
                                    string message = "AnalysisHtml中，" + RealShipRaw.Area + "中" + Cols.Key + "的引用文件没找到！";
                                    ErrorDatas errorDatas = new ErrorDatas(EnumPlatform.Amazon, null, null, message);
                                    DBContext.ErrorDatas.Add(errorDatas);
                                }
                            }
                            DBContext.RealShip.UpdateRange(UpdateRealShips.ToArray());
                            DBContext.SaveChanges();
                        }

                        if (RealShipRaw.Area == "co.uk")//添加欧洲五国其他四国的关系
                        {
                            //删除name==服饰productRaw数据，realship也删除
                            var ship = DBContext.RealShip.Where(x => x.Area == "co.uk" && x.Name == "服饰").FirstOrDefault();
                            if (ship is not null)
                            {
                                DBContext.ProductRaw.Remove(ship.ProductRaw!);
                                DBContext.RealShip.Remove(ship);
                                DBContext.SaveChanges();
                            }
                        }
                        return new JsonResult("{'result':'1','msg':'解析完成'}");
                    }
                    else
                    {
                        return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundThead]'}");
                    }
                }
                else
                {
                    return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundTr]'}");
                }
            }
            else
            {
                return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundTable]'}");
            }
        }

        private IActionResult SeparatelyUploaded(int id, RealShipRaw? RealShipRaw, short tableIndex, short nameIndex, short BTGIndex, short CCTIndex)
        {
            //分开上传的，要先上传cct，再上传btg内容
            //获取CCT内容，入库
            Sy.String Page = Encoding.UTF8.GetString(RealShipRaw!.FileContent!);
            Sy.String btgPage = Encoding.UTF8.GetString(RealShipRaw!.BTGFileContent!);

            var TableMidor = new Midor("<table", "</table>");
            string[] str = new string[] { "<table", "</table>" };
            var RowMidor = new Midor("<tr", "</tr>");
            var aMidor = new Midor("<a", "</a>");
            var nMidor = new Midor(">", "</a>");

            const string TdEnd = "</td>";
            const string aEnd = "</a>";
            Dictionary<string, Sy.String[]> dicRowCCTful = new();
            Dictionary<string, Sy.String[]> dicRowBTGful = new();

            //获取CCT内容
            if (Page.CanMid(TableMidor))
            {
                Sy.String TableContent = null;
                switch (tableIndex)
                {
                    case 3:  //取第三个table
                        TableContent = Page.SplitEx(str, 5);
                        break;
                    case -1:  //取最后一个table
                        TableContent = Page.Mid(TableMidor, Sy.StringMidOptions.StartFormBack_EndFormFront);
                        break;
                }
                if (TableContent!.CanMid(RowMidor))
                {
                    var Enumerator = TableContent.GetMidEnumerator(RowMidor);
                    if (Enumerator.MoveNext())
                    {
                        dicRowCCTful = getRows(nameIndex, Enumerator);
                    }
                    else
                    {
                        return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundThead]'}");
                    }
                }
                else
                {
                    return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundTr]'}");
                }
            }
            else
            {
                return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundTable]'}");
            }

            //获取BTG内容
            if (btgPage.CanMid(TableMidor))
            {
                Sy.String TableContent = null;
                switch (tableIndex)
                {
                    case 3:  //取第三个table
                        TableContent = btgPage.SplitEx(str, 5);
                        break;
                    case -1:  //取最后一个table
                        TableContent = btgPage.Mid(TableMidor, Sy.StringMidOptions.StartFormBack_EndFormFront);
                        break;
                }
                if (TableContent!.CanMid(RowMidor))
                {
                    var Enumerator = TableContent.GetMidEnumerator(RowMidor);
                    if (Enumerator.MoveNext())
                    {
                        dicRowBTGful = getRows(nameIndex, Enumerator);
                    }
                    else
                    {
                        return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundThead]'}");
                    }
                }
                else
                {
                    return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundTr]'}");
                }
            }
            else
            {
                return new JsonResult("{'result':'-1','msg':'解析内容出错[NotFoundTable]'}");
            }

            if (dicRowCCTful.Count > 0 && dicRowBTGful.Count > 0)
            {
                List<RealShip> RealShips = new();
                List<NodeRaw> NodeRaws = new();
                List<ProductRaw> ProductRaws = new();
                const string Href = "href=\"";
                foreach (var Cols in dicRowCCTful)
                {                   
                    ProductRaw LastProductRaw = null;                                     
                    var keyName = Cols.Key;
                    var CCT = Cols.Value[CCTIndex];
                    var count = CCT.Split(aEnd).Length - 1;
                    if (count == 1)
                    {
                        ProductRaws.Add(LastProductRaw = new ProductRaw(RealShipRaw, CCT.Mid(Href),keyName));
                    }
                    else
                    {
                        throw new Exception("存在多个excel链接！");
                    }                                   
                }
                foreach (var Cols in dicRowBTGful)
                {
                    NodeRaw LastNodeRaw = null;
                    var keyName = Cols.Key;
                    var BTG = Cols.Value[BTGIndex];
                    var count = BTG.Split(aEnd).Length - 1;                
                    if (count == 1)
                    {
                        NodeRaws.Add(LastNodeRaw = new NodeRaw(RealShipRaw, BTG.Mid(Href), keyName));
                    }
                    else
                    {
                        throw new Exception("存在多个excel链接！");
                    }
                }

                #region
                //foreach (var Cols in dicRowCCTful)
                //{
                //    NodeRaw LastNodeRaw = null;
                //    ProductRaw LastProductRaw = null;
                //    const string Href = "href=\"";
                //    Dictionary<string, ProductRaw> multiProducts = new();
                //    List<NodeRaw> multiNodes = new List<NodeRaw>();
                //    var keyName = Cols.Key;
                //    var CCT = Cols.Value[CCTIndex];                    
                //    string name= keyName.Replace("<td>", "").Replace("</td>", "").Replace("*","").Split("（")[0].Trim();
                //    var count = CCT.Split(aEnd).Length - 1;
                //    if (count == 1)
                //    {
                //        ProductRaws.Add(LastProductRaw = new ProductRaw(RealShipRaw, CCT.Mid(Href)));
                //    }
                //    else
                //    {
                //        LastProductRaw = null;
                //    }
                //    var btgRow= dicRowBTGful.Where(x => x.Key.Contains(name)).FirstOrDefault();
                //    if(btgRow.IsNotNull() && btgRow.Value is not null)
                //    {
                //        var btg = btgRow.Value[BTGIndex];
                //        var bcount = btg.Split(aEnd).Length - 1;
                //        if (bcount == 1)
                //        {
                //            NodeRaws.Add(LastNodeRaw = new NodeRaw(RealShipRaw, btg.Mid(Href)));
                //        }
                //        else
                //        {
                //            LastNodeRaw = null;
                //        }                                            
                //    }                 
                //    RealShips.Add(new(RealShipRaw, LastNodeRaw!, LastProductRaw!, Cols.Key));
                //}
                #endregion
               
                DBContext.ProductRaw.AddRange(ProductRaws);
                DBContext.NodeRaw.AddRange(NodeRaws);
                DBContext.SaveChanges();
            }
            else
            {
                return new JsonResult("{'result':'-1','msg':'[no data]'}");
            }
            return new JsonResult("{'result':'1','msg':'解析完成'}");
        }

        private Dictionary<string, Sy.String[]> getRows(short nameIndex, IEnumerator<Sy.String> Enumerator)
        {
            const string TdEnd = "</td>";
            const string aEnd = "</a>";
            Dictionary<string, Sy.String[]> dicRow = new();
            //解析Thead->Tr->Th                                                                     
            string name = "";
            while (Enumerator.MoveNext())
            {
                if (Enumerator.Current.Contains(aEnd))
                {
                    var Temp = Enumerator.Current.Split(TdEnd);
                    if (Temp[nameIndex] is not null)
                    {
                        if (Temp[nameIndex].Contains("</a>"))
                        {
                            name = Temp[nameIndex].Mid("<a", "</a>").Split('>')[1];
                        }
                        else if (Temp[nameIndex].Contains("<strong>"))
                        {
                            name = Temp[nameIndex].Mid("<strong>", "</strong>");
                        }
                        else
                        {
                            name = Temp[nameIndex].Replace("<td>", "").Replace("\r\n", "").Replace(">", "").Trim();
                        }
                        dicRow.Add(name, Temp);
                    }
                }
            };
            return dicRow;
        }

        /// <summary>
        /// 第三步：下载到node表，product表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        [HttpGet]
        [Route("/Amazon/DownloadExcel")]
        public IActionResult DownloadExcel(int id, string type)
        {
            HttpClient client = new HttpClient();
            try
            {
                string msg = "";
                if (type == "n")
                {
                    var NodeRaw = DBContext.NodeRaw.Where(x => x.ID == id).FirstOrDefault();
                    if (NodeRaw.IsNotNull())
                    {
                        var files = client.GetByteArrayAsync(NodeRaw.URL);
                        NodeRaw.FileContent = files.Result;
                        NodeRaw.Length = files.Result.Length;
                        NodeRaw.GetTime = DateTime.Now;
                        DBContext.NodeRaw.Update(NodeRaw);
                        DBContext.SaveChanges();
                        msg = "Node Excel 下载成功 ";
                    }
                    else
                    {
                        return new JsonResult("{'result':'-2','msg':'[NodeID Error]'}");
                    }
                }
                else if (type == "p")
                {
                    var ProductRaw = DBContext.ProductRaw.Where(x => x.ID == id).FirstOrDefault();
                    if (ProductRaw.IsNotNull())
                    {
                        var files = client.GetByteArrayAsync(ProductRaw.URL);
                        ProductRaw.FileContent = files.Result;
                        ProductRaw.Length = files.Result.Length;
                        ProductRaw.GetTime = DateTime.Now;
                        DBContext.ProductRaw.Update(ProductRaw);
                        DBContext.SaveChanges();
                        msg = "Product Excel 下载成功 ";
                    }
                    else
                    {
                        return new JsonResult("{'result':'-2','msg':'ProductID Erro'}");
                    }
                }
                else
                {
                    return new JsonResult("{'result':'-2','msg':'Parameter Error'}");
                }
                return new JsonResult("{'result':'1','msg':'"+ msg + "'}");
            }
            catch (Exception e)
            {
                logger.LogError("下载excel出错，ex：" + e.Message);
                return new JsonResult("{'result':'-3','msg':'Download Error'}");
            }
        }

        [HttpGet]
        [Route("/Amazon/GetNodeExcelList")]
        public IEnumerable<NodeRaw> GetNodeExcelList(int id) =>
        DBContext.NodeRaw.Where(x => x.RealShipRaw.ID == id).ToList();

        [HttpGet]
        [Route("/Amazon/analysisNExcelClick")]
        public IActionResult analysisNExcelClick(int id, int rID)
        {
            try
            {
                //获取execl内容，解析
                var n = DBContext.NodeRaw.Where(x => x.ID == id).FirstOrDefault();
                if (n.IsNotNull())
                {
                    List<Node> nodes = new List<Node>();
                    Dictionary<int, keyVal> keyValues = new Dictionary<int, keyVal>();
                    for (int i = 0; i < 10; i++)
                    {
                        keyValues.Add(i, new keyVal());
                    }
                    int rSRID = rID;
                    int formRawID = n.ID;
                    string fileName = n.Name;
                    using (MemoryStream memStream = new MemoryStream(n.FileContent))
                    {
                        IWorkbook workbook = null;
                        string _ext = fileName.Substring(fileName.LastIndexOf("."), fileName.Length - fileName.LastIndexOf(".")).ToLower();
                        if (_ext == ".xlsx" || _ext == ".xlsm")
                        {
                            workbook = new XSSFWorkbook(memStream);
                        }
                        else
                        {
                            workbook = new HSSFWorkbook(memStream);
                        }
                        ISheet sheet = workbook.GetSheetAt(1);
                        IRow ITitleRow = sheet.GetRow(0);  //第一行，列名
                        int totalColumn = ITitleRow.LastCellNum;
                        int totalRow = sheet.LastRowNum;
                        string _value = string.Empty;
                        string _type = string.Empty;
                        for (int i = 1; i <= totalRow; i++)
                        {
                            string nameNext = "";
                            IRow row = sheet.GetRow(i);
                            IRow rowNext = sheet.GetRow(i + 1);
                            if (i != totalRow)  //最后一行就不赋值了，直接置为空
                            {
                                nameNext = rowNext.GetCell(1).ToString();
                            }

                            if (row == null)
                            {
                                continue;
                            }
                            string forName = row.GetCell(1).ToString();
                            string name = "";
                            if (!forName.Contains("/"))
                            {
                                name = forName;
                            }
                            else
                            {
                                name = forName.Substring(forName.LastIndexOf("/") + 1, forName.Length - forName.LastIndexOf("/") - 1);
                            }
                            string value = row.GetCell(0).ToString();
                            int level = forName.Split('/').Length - 1;
                            if (level == 0)
                            {
                                //parentid  null，直接入库
                                keyValues[0].Name = forName;
                                Node n0 = new Node(null, formRawID, rSRID, forName, name, value);
                                DBContext.Node.Add(n0);
                                DBContext.SaveChanges();
                                keyValues[0].ID = n0.ID;
                            }
                            else
                            {
                                keyValues[level].Name = forName;
                                Node n1 = new Node(keyValues[level - 1].ID, formRawID, rSRID, forName, name, value);
                                if (!nameNext.Contains(forName))
                                {
                                    //是终结点，存入list中
                                    nodes.Add(n1);

                                }
                                else
                                {
                                    //非终结点，save，更新id              
                                    DBContext.Node.Add(n1);
                                    DBContext.SaveChanges();
                                    keyValues[level].ID = n1.ID;
                                }
                            }
                        }
                        DBContext.Node.AddRange(nodes);
                        n.AnsiTime = DateTime.Now;
                        DBContext.NodeRaw.Update(n);
                        DBContext.SaveChanges();
                    }
                }
                return new JsonResult("{'result':'1','msg':'excel解析成功'}");
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
                logger.LogError("node:" + id.ToString() + "的excel解析失败，" + e.Message);
                return new JsonResult("{'result':'-1','msg':'excel解析失败'}");
            }
        }

        [HttpGet]
        [Route("/Amazon/GetProductExcelList")]
        public IEnumerable<ProductRaw> GetProductExcelList(int id) =>
         DBContext.ProductRaw.Where(x => x.RealShipRaw.ID == id).ToList();


        /// <summary>
        /// 解析product的excel文件
        /// </summary>
        /// <param name="id"></param>
        /// <param name="rID"></param>
        /// <returns></returns>
        [HttpGet]
        [Route("/Amazon/analysisPExcelClick")]
        public IActionResult analysisPExcelClick(int id, int rID)
        {
            try
            {
                var p = DBContext.ProductRaw.Where(x => x.ID == id && x.AnsiTime==null).FirstOrDefault();
                if (p.IsNotNull())
                {                                                          
                    using (MemoryStream memStream = new MemoryStream(p.FileContent))
                    {
                        string _ext = p.Name.Substring(p.Name.LastIndexOf("."), p.Name.Length - p.Name.LastIndexOf(".")).ToLower();
                        if (_ext == ".xlsx" || _ext == ".xlsm")
                        {
                            XSSFWorkbook xSSF = new XSSFWorkbook(memStream);

                            #region add attribute
                            ISheet sheetAttribute = xSSF.GetSheet("Data Definitions");
                            AnalysisAttribute(sheetAttribute, p.ID, rID);
                            #endregion

                            #region add category,add value
                            ISheet sheetProductType = xSSF.GetSheet("Valid Values");
                            AnalysisProductType(sheetProductType, p.ID, rID);
                            #endregion

                            #region add categoryAttrRelation,update addtribute
                            AddCategoryAttrRelationWithIsMandatory(p.ID, rID, xSSF);
                            #endregion

                            //更新product的ansiTime
                            p.AnsiTime = DateTime.Now;
                            DBContext.ProductRaw.Update(p);
                            DBContext.SaveChanges();
                        }
                        else
                        {
                            IWorkbook workbook = new HSSFWorkbook(memStream);
                            return new JsonResult("{'result':'-1','msg':'特殊的excel文件'}");
                        }
                    }
                }
                return new JsonResult("{'result':'1','msg':'产品序号为{"+id+"}的excel解析成功'}");
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
                logger.LogError("product:" + id + "的excel解析失败，" + e.Message);
                return new JsonResult("{'result':'-1','msg':'产品序号为{" + id + "}的excel解析失败'}");
            }
        }

        private Regex RegRangeNumber = new Regex(@"[\d]{1,}");
        private string ClearName(string name)
        {
            Sy.String s = name;
            return s.RegReplace(RegRangeNumber, "");
        }
        //'Dropdown Lists'!$DM$4:$DM$8
        private Regex RegRangePosition = new Regex(@"[\d]{1,}");
        private Regex RegRange = new Regex(@"[\w]{1,}");
        private int []  GetPosition(string name,string str)
        {
            try
            {
                if(str== "sportsmemorabiliacondition_type")
                {

                }
                int[] arrayInt = new int[4];
                MatchCollection collection = RegRange.Matches(name.Replace("'Dropdown Lists'!", ""));
                if(collection.Count()==4)
                {
                    int num1 = DBExcute.parseIntByCode(collection[0].Value);
                    arrayInt[0] = num1 - 1;
                    arrayInt[1] = Convert.ToInt32(collection[1].Value) - 1;
                    int num3 = DBExcute.parseIntByCode(collection[2].Value);
                    arrayInt[2] = num3 - 1;
                    arrayInt[3] = Convert.ToInt32(collection[3].Value) - 1;
                }
                else if(collection.Count()==2)
                {
                    int num1 = DBExcute.parseIntByCode(collection[0].Value);
                    arrayInt[0] = num1 - 1;
                    arrayInt[1] = Convert.ToInt32(collection[1].Value) - 1;
                    int num3 = DBExcute.parseIntByCode(collection[0].Value);
                    arrayInt[2] = num3 - 1;
                    arrayInt[3] = Convert.ToInt32(collection[1].Value) - 1;
                }
              
                return arrayInt;
            }
            catch(Exception e)
            {
             
                return null;
            }            
        }


        /// <summary>
        /// add category,add value
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="formRawID"></param>
        /// <param name="rID"></param>
        /// <returns></returns>
        int AnalysisProductType(ISheet sheet, int formRawID, int rID)
        {
            try
            {
                var valueList = DBContext.Value.Where(x => x.Platform == EnumPlatform.Amazon).ToList();
                List<Value> values = new List<Value>();
                List<string> datas = new List<string>();
                int totalRow = sheet.LastRowNum;
                for (int i = 0; i <= totalRow; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row.GetCell(0) is not null && row.GetCell(0).ToString() != "")  //没有值或者是分组行，直接跳过
                    {
                        continue;
                    }
                    int totalCell = row.LastCellNum;  //每一行的单元格总数                                                      
                    if (i == 1)  //add category
                    {
                        GetCategory(formRawID, rID, row);
                    }
                    else
                    {
                        //搭建value元数据,获取所有Value值                        
                        for (int j = 2; j < totalCell; j++)
                        {
                            string value = row.GetCell(j).ToString();
                            int cnt = datas.Where(x => x == value).Count();
                            int num = valueList.Where(x => x.OriginalName == value).Count();
                            if (cnt <= 0 && num <= 0)
                            {
                                Value valueModel = new Value(value, value, EnumPlatform.Amazon, formRawID, rID);
                                values.Add(valueModel);
                            }
                        }
                    }
                }
                DBContext.Value.AddRange(values);
                DBContext.SaveChanges();
                return 1;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
                logger.LogError("解析产品Excel失败，原因：" + e.Message);
                return -1;
            }
        }
        private void GetCategory(int formRawID, int rID, IRow row)
        {
            List<Category> Categories = new List<Category>();
            for (int j = 2; j < row.LastCellNum; j++)
            {
                string name = row.GetCell(j).StringCellValue;
                int count = DBContext.Category.Where(x => x.OriginalName == name && x.Platform == EnumPlatform.Amazon && x.AmzFormRawID == formRawID && x.AmzRSRID == rID).Count();
                if (count <= 0)
                {
                    Category newCategory = new Category(name, name, EnumPlatform.Amazon, formRawID, rID);
                    Categories.Add(newCategory);
                }
            }
            DBContext.Category.AddRange(Categories);
            DBContext.SaveChanges();
        }



        /// <summary>
        /// 从Data Definitions表中，获取attribute说明信息
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="formRawID"></param>
        /// <param name="rID"></param>
        /// <returns></returns>
        int AnalysisAttribute(ISheet sheet, int formRawID, int rID)
        {
            try
            {
                List<Attribute> attributeList = new List<Attribute>();
                int totalRow = sheet.LastRowNum;  //总行数
                int number = sheet.FirstRowNum;
                for (int i = 3; i <= totalRow; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row.GetCell(0).StringCellValue != "")
                    {
                        continue;
                    }
                    string DName = row.GetCell(2).ToString().Trim();
                    string definitionAndUse = row.GetCell(3).ToString().Trim();
                    string acceptedValues = row.GetCell(4).ToString().Trim();
                    string example = row.GetCell(5).ToString().Trim();
                    string ONname = row.GetCell(1).ToString().Trim();
                    if (ONname.Contains('-'))
                    {
                        var arrayStrs = ONname.Split('-');
                        if (arrayStrs.Length == 2)
                        {
                            string b = arrayStrs[0].Trim();
                            string e = arrayStrs[1].Trim();
                            string fname = b.Substring(0, b.Length - 1);
                            if (int.TryParse(b.Replace(fname, ""), out int begin) && int.TryParse(e.Replace(fname, ""), out int end))
                            {
                                for (int j = begin; j <= end; j++)
                                {
                                    string fieldName = fname + j.ToString();
                                    AddAttribute(formRawID, rID, attributeList, DName, definitionAndUse, acceptedValues, example, fieldName);
                                }
                            }
                            else
                            {
                                throw new Exception($"{ONname}解析失败！");
                            }
                        }
                        else
                        {
                            throw new Exception($"{ONname}解析失败！");
                        }
                    }
                    else
                    {
                        AddAttribute(formRawID, rID, attributeList, DName, definitionAndUse, acceptedValues, example, ONname);
                    }
                }
                DBContext.Attribute.AddRange(attributeList);
                DBContext.SaveChanges();
                return 1;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
                logger.LogError("解析产品属性失败，原因：" + e.Message);
                return -1;
            }
        }
        private void AddAttribute(int formRawID, int rID, List<Attribute> attributeList, string DName, string definitionAndUse, string acceptedValues, string example, string ONname)
        {
            int count = DBContext.Attribute.Where(x => x.Platform == EnumPlatform.Amazon && x.OriginalName == ONname && x.AmzFormRawID == formRawID && x.AmzRSRID == rID).Count();
            if (count <= 0)
            {
                Attribute attribute = new Attribute(ONname, DName, EnumInputType.STRING_TYPE,
                          EnumFormatType.NORMAL, EnumDateFormatType.None, EnumType.TEXT_FILED, "", "",
                          formRawID, rID, EnumPlatform.Amazon, definitionAndUse, acceptedValues, example,null);
                attributeList.Add(attribute);
            }
        }

        void AddCategoryAttrRelationWithIsMandatory(int formRawID, int rID, XSSFWorkbook xSSF)
        {
            List<ValueShow> valuelsts = new List<ValueShow>();
            List<CategoryAttrRelation> categoryAttrRelations = new();
            var categorys = DBContext.Category.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzFormRawID == formRawID && x.AmzRSRID == rID).ToList();
            var attributes = DBContext.Attribute.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzFormRawID == formRawID && x.AmzRSRID == rID).ToList();
            var values = DBContext.Value.Where(x => x.Platform == EnumPlatform.Amazon).ToList();

            var templateSheet = xSSF.GetSheet("Template");
            var DropdownListsSheet = xSSF.GetSheet("Dropdown Lists");
            var ConditionalFormatting = templateSheet.SheetConditionalFormatting;
            int numbers = ConditionalFormatting.NumConditionalFormattings;

            AmazonBase.AttributeMapRange MapRange = new AmazonBase.AttributeMapRange(xSSF, formRawID, rID, DBContext);
            AmazonBase.FormulaFunctions Functions = new AmazonBase.FormulaFunctions();
            IRow row = templateSheet.GetRow(2);
            FormulaResult result = new FormulaResult();
            for (int i = 2; i < row.LastCellNum; i++)//1-351     取fieldname字段
            {
                bool flag = false;
                string attributeName = row.GetCell(i).StringCellValue;                
                var attr = attributes.Where(x => x.OriginalName == attributeName).FirstOrDefault();
                if (attr == null)
                {
                    //throw new Exception($"{attributeName}属性没找到");//watch   prop_65
                    string message = $"执行AddCategoryAttrRelationWithIsMandatory方法时，formRawID:{formRawID},rID:{rID}下excel中的template中的{attributeName}属性在Data Definitions没找到";
                    ErrorDatas err = new ErrorDatas(EnumPlatform.Amazon, formRawID, rID, message);
                    DBContext.ErrorDatas.Add(err);
                }
                else
                {
                    foreach (var c in categorys)
                    {
                        string valueDatas = "";
                        //获取下拉框的值
                        var workBook = xSSF.GetCTWorkbook();
                        var defineNames = workBook.definedNames.definedName;
                        //List<CT_DefinedName>
                        string attrName = ClearName(attributeName);
                        string name = c.OriginalName + attrName;
                        var define = defineNames.Where(x => x.name == name).FirstOrDefault();
                        if (define != null)
                        {
                            int[] array = GetPosition(define.Value, name);
                            //根据DM3-DM4，在dropdowmList表中取值
                            for (int n = array[1]; n <= array[3]; n++)
                            {
                                IRow cells = DropdownListsSheet.GetRow(n);
                                string value = cells.GetCell(array[0]).StringCellValue;
                                //取值，获取datas
                                var v = values.Where(v => v.OriginalName == value).Select(x =>
                                  new ValueShow { Id = x.Id, OriginalName = x.OriginalName, DisplayName = x.DisplayName }).FirstOrDefault();
                                if (v is not null)
                                {
                                    valuelsts.Add(v);
                                }
                                else
                                {
                                    //throw new Exception($"{attributeName}属性没找到");//watch   prop_65
                                    string message = $"执行AddCategoryAttrRelationWithIsMandatory方法时，formRawID:{formRawID},rID:{rID}下excel中的value:{value}值没在数据库中找到";
                                    ErrorDatas err = new ErrorDatas(EnumPlatform.Amazon, formRawID, rID, message);
                                    DBContext.ErrorDatas.Add(err);
                                }
                            }
                            if (valuelsts.Count() > 0)
                            {
                                flag = true;
                                valueDatas = DBExcute.getStringByArray(valuelsts);
                            }
                        }                      
                        //根据属性和产品分类获取每个
                        var relations = DBContext.CategoryAttrRelation.Where(x => x.Platform == EnumPlatform.Amazon && x.CId == c.Id
                                                                                && x.AmzFormRawID == formRawID && x.AmzRSRID == rID).ToList();
                        //获取每一格得管理规则
                        var conditionalFormatting = ConditionalFormatting.GetConditionalFormattingAt(i - 1); //0-350
                        var counts = conditionalFormatting.NumberOfRules;
                        //验证规则，返回最后一个为true的
                        for (int j = 0; j < counts; j++)
                        {
                            var rule = conditionalFormatting.GetRule(j);
                            string ruleRaw = rule.Formula1 + rule.Formula2;
                            FormulaResult currentResult = Functions.Invoke(ruleRaw, c.OriginalName, attributeName, MapRange);
                            if (currentResult.Result)
                            {
                                result = currentResult;
                            }
                        }
                        CategoryAttrRelation categoryAttr = new CategoryAttrRelation(c.Id, attr, null, EnumPlatform.Amazon,
                                                                                         result.Ship, null, formRawID, rID, valueDatas);
                        var count = relations.Where(x => x.Platform == EnumPlatform.Amazon && x.CId == c.Id &&
                        x.AttrId == attr.Id).Count();
                        if (count <= 0)
                        {
                            categoryAttrRelations.Add(categoryAttr);
                        }
                    }                    
                    if (flag)
                    {
                        //需要更新attribute为select
                        attr.Type = EnumType.SELECT_COMBO_BOX;
                        attributes.Add(attr);
                       
                    }
                }                                          
            }
            try
            {
                DBContext.Attribute.UpdateRange(attributes);
                DBContext.CategoryAttrRelation.AddRange(categoryAttrRelations);
                DBContext.SaveChanges();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
               
            }
        }

        void UpdateAttributeDatas(ISheet sheetProductType, int formRawID, int rSRID)
        {
            Stopwatch stopwatch = new Stopwatch();
            try
            {
                List<Attribute> attributes = new List<Attribute>();
                List<CategoryAttrRelation> relations = new List<CategoryAttrRelation>();
                stopwatch.Start();
                var categoryLists = DBContext.Category.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzFormRawID == formRawID && x.AmzRSRID == rSRID).ToList();
                var attributeLists = DBContext.Attribute.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzFormRawID == formRawID && x.AmzRSRID == rSRID).ToList();
                var valueLists = DBContext.Value.Where(x => x.Platform == EnumPlatform.Amazon).ToList();
                var relationLists = DBContext.CategoryAttrRelation.Where(x =>x.attribute.AmzFormRawID == formRawID && x.attribute.AmzRSRID == rSRID).ToList();
                stopwatch.Stop();

                Console.WriteLine($"获取基础数据：{stopwatch.ElapsedMilliseconds}");

                stopwatch.Start();
                int totalRow = sheetProductType.LastRowNum;
                for (int i = 2; i <= totalRow; i++)
                {
                    List<Value> values = new List<Value>();
                    IRow row = sheetProductType.GetRow(i);
                    string name = row.GetCell(1).StringCellValue;
                    string displayName = "", category = "";
                    int totalCells = row.LastCellNum;
                    if (string.IsNullOrWhiteSpace(name))
                    {
                        continue;
                    }
                    Console.WriteLine($"{name}行数据量:{totalCells}");
                    ////获取value的json值
                    for (int j = 1; j < totalCells - 1; j++)
                    {                       
                        string s = row.Cells[j].StringCellValue;
                        if (s == "")
                        {
                            break;
                        }
                        var v = valueLists.Where(v => v.OriginalName == row.Cells[j].StringCellValue).Select(x =>
                          new Value { Id = x.Id, OriginalName = x.OriginalName, DisplayName = x.DisplayName }).First();
                        if (v is not null)
                        {
                            values.Add(v);
                        }
                    }

                    stopwatch.Start();
                    string valueStr = DBExcute.getStringByArray(values);
                    if (name.Contains('-'))
                    {
                        string[] arrStr = name.Split("- [");
                        displayName = arrStr[0].ToString().Trim();
                        category = arrStr[1].ToString().Replace("]", "").Trim();
                        var relation = relationLists.Where(x => x.attribute.DisplayName == displayName && x.category.OriginalName == category).ToList();
                        foreach (var r in relation)
                        {
                            r.Values = valueStr;
                            relations.Add(r);
                            //DBContext.CategoryAttrRelation.Update(r);
                        }
                    }
                    else
                    {
                        displayName = name;
                        category = "";
                        //根据找到的attribute，去更新涉及到的所有relation
                        var relation = relationLists.Where(x => x.attribute.DisplayName == displayName).ToList();
                        foreach (var r in relation)
                        {
                            r.Values = valueStr;
                            relations.Add(r);
                            //DBContext.CategoryAttrRelation.Update(r);
                        }
                    }
                    stopwatch.Stop();
                    Console.WriteLine($"所有执行完毕：{stopwatch.ElapsedMilliseconds}");
                    DBContext.CategoryAttrRelation.UpdateRange(relations);
                    var attrs = attributeLists.Where(x => x.DisplayName == displayName).ToList();
                    foreach (var attr in attrs)
                    {
                        attr.Type = EnumType.SELECT_COMBO_BOX;
                        attributes.Add(attr);
                        //DBContext.Attribute.Update(attr);
                    }
                    DBContext.Attribute.UpdateRange(attributes);
                }               
                DBContext.SaveChanges();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }



        [HttpGet]
        [Route("/Amazon/ClearProductData")]
        public IActionResult ClearProductData(int formId, int rid)
        {
            try
            {
                //清除AttrValueRelation
                var valueRelations = DBContext.AttrValueRelation.Where(x => x.Value.Platform == EnumPlatform.Amazon && x.Value.AmzRSRID == rid && x.Value.AmzFormRawID == formId).ToList();
                if (valueRelations.IsNotNull())
                {
                    DBContext.RemoveRange(valueRelations);
                }

                var value = DBContext.Value.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzRSRID == rid && x.AmzFormRawID == formId).ToList();
                if (value.IsNotNull())
                {
                    DBContext.RemoveRange(value);
                }

                //清除CategoryAttrRelation
                var attrRelations = DBContext.CategoryAttrRelation.Where(x => x.attribute.Platform == EnumPlatform.Amazon && x.attribute.AmzRSRID == rid && x.attribute.AmzFormRawID == formId).ToList();
                if (attrRelations.IsNotNull())
                {
                    DBContext.RemoveRange(attrRelations);
                }

                var attribute = DBContext.Attribute.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzRSRID == rid && x.AmzFormRawID == formId).ToList();
                if (attribute.IsNotNull())
                {
                    DBContext.RemoveRange(attribute);
                }

                //清除AreaCategoryRelation
                var categoryRelations = DBContext.AreaCategoryRelation.Where(x => x.Category.Platform == EnumPlatform.Amazon && x.Category.AmzRSRID == rid && x.Category.AmzFormRawID == formId).ToList();
                if (categoryRelations.IsNotNull())
                {
                    DBContext.RemoveRange(categoryRelations);
                }

                var Category = DBContext.Category.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzRSRID == rid && x.AmzFormRawID == formId).ToList();
                if (Category.IsNotNull())
                {
                    DBContext.RemoveRange(Category);
                }

                var p = DBContext.ProductRaw.Where(x => x.ID == formId).FirstOrDefault();
                if (p is not null)
                {
                    p.AnsiTime = null;
                    DBContext.ProductRaw.Update(p);
                    DBContext.SaveChanges();
                }
                return new JsonResult("{'result':'1','msg':'删除成功'}");
            }
            catch (Exception e)
            {
                return new JsonResult("{'result':'-1','msg':'删除失败'}");
            }
        }


        [HttpGet]
        [Route("/Amazon/ClearNodeData")]
        public IActionResult ClearNodeData(int formId, int rid)
        {
            try
            {
                //清除Node
                var nodes = DBContext.Node.Where(x => x.FormRawID == formId && x.RSRID == rid).ToList();
                if (nodes.IsNotNull())
                {
                    DBContext.RemoveRange(nodes);
                }
                var p = DBContext.NodeRaw.Where(x => x.ID == formId).FirstOrDefault();
                if (p is not null)
                {
                    p.AnsiTime = null;
                    DBContext.NodeRaw.Update(p);
                    DBContext.SaveChanges();
                }
                return new JsonResult("{'result':'1','msg':'删除成功'}");
            }
            catch (Exception e)
            {
                return new JsonResult("{'result':'-1','msg':'删除失败'}");
            }
        }

        /// <summary>
        /// 根据地区id，清空该地区下的所有node和product信息
        /// </summary>
        /// <param name="rid"></param>
        /// <returns></returns>
        [HttpGet]
        [Route("/Amazon/ClearData")]
        public IActionResult ClearData(int rid)
        {
            //清除AttrValueRelation
            var valueRelations = DBContext.AttrValueRelation.Where(x => x.Value.Platform == EnumPlatform.Amazon && x.Value.AmzRSRID == rid).ToList();
            if (valueRelations.IsNotNull())
            {
                DBContext.RemoveRange(valueRelations);
            }

            var value = DBContext.Value.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzRSRID == rid).ToList();
            if (value.IsNotNull())
            {
                DBContext.RemoveRange(value);
            }

            //清除CategoryAttrRelation
            var attrRelations = DBContext.CategoryAttrRelation.Where(x => x.attribute.Platform == EnumPlatform.Amazon && x.attribute.AmzRSRID == rid).ToList();
            if (attrRelations.IsNotNull())
            {
                DBContext.RemoveRange(attrRelations);
            }

            var attribute = DBContext.Attribute.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzRSRID == rid).ToList();
            if (attribute.IsNotNull())
            {
                DBContext.RemoveRange(attribute);
            }

            //清除AreaCategoryRelation
            var categoryRelations = DBContext.AreaCategoryRelation.Where(x => x.Category.Platform == EnumPlatform.Amazon && x.Category.AmzRSRID == rid).ToList();
            if (categoryRelations.IsNotNull())
            {
                DBContext.RemoveRange(categoryRelations);
            }

            var Category = DBContext.Category.Where(x => x.Platform == EnumPlatform.Amazon && x.AmzRSRID == rid).ToList();
            if (Category.IsNotNull())
            {
                DBContext.RemoveRange(Category);
            }


            //Node
            var Node = DBContext.Node.Where(x => x.RSRID == rid).ToList();
            if (Node.IsNotNull())
            {
                DBContext.RemoveRange(Node);
            }

            //NodeRaw
            var NodeRaw = DBContext.NodeRaw.Where(x => x.RealShipRaw.ID == rid).ToList();
            if (NodeRaw.IsNotNull())
            {
                DBContext.RemoveRange(NodeRaw);
            }

            //ProductRaw
            var ProductRaw = DBContext.ProductRaw.Where(x => x.RealShipRaw.ID == rid).ToList();
            if (ProductRaw.IsNotNull())
            {
                DBContext.RemoveRange(ProductRaw);
            }

            //RealShip
            var RealShip = DBContext.RealShip.Where(x => x.RealShipRaw.ID == rid).ToList();
            if (RealShip.IsNotNull())
            {
                DBContext.RemoveRange(RealShip);
            }

            //RealShipRaw
            var RealShipRaw = DBContext.RealShipRaw.Where(x => x.ID == rid).ToList();
            if (RealShipRaw.IsNotNull())
            {
                DBContext.RemoveRange(RealShipRaw);
            }
            DBContext.SaveChanges();
            return new JsonResult("{'result':'1','msg':'删除成功'}");
        }
    }
}
